﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Dotnet.DB
{
    public partial class DbBase : ISchema
    {
        public DataTable GetDbSchema()
        {
            return GetDbSchema("");
        }
        public virtual DataTable GetDbTables()
        {
            return GetDbSchema("Tables");
        }
        protected virtual List<string> GetTableKey(string tableName,DataRow row)
        {
            StringBuilder sb = new StringBuilder();
            DataTable dt = GetDbColumns();
            var temp = (from item in dt.AsEnumerable() where item.Field<string>("TABLE_NAME") == tableName select item);
            dt = temp.CopyToDataTable();
            foreach(DataColumn dc in dt.Columns)
            {

            }
            return null;
            //throw new Exception("未实现");
        }
        public virtual DataTable GetTablesJoinLocalTables(DataTable dtSchema, DataTable dtLocal,string dbName)
        {
            DataTable dt = dtLocal.DefaultView.ToTable();
            dt.Rows.Clear();
            foreach (DataRow item in dtSchema.Rows)//从架构中获取的表信息
            {
                string tableName = item.Field<string>("TABLE_NAME");
                var rows = dtLocal.AsEnumerable().Where(t => t.Field<string>("table_name") == tableName && t.Field<string>("db_name") == dbName).ToList();
                if (rows.Count() > 0)
                {
                    rows[0]["table_desc_type"] = dbName;
                    dt.Rows.Add(rows[0].ItemArray);
                }
                else
                {
                    rows = dtLocal.AsEnumerable().Where(t => t.Field<string>("table_name") == tableName).ToList();
                    if (rows.Count() > 0)
                    {
                        DataRow row = rows[0];
                        row["id"] = "";
                        row["table_desc"] = $"{row["table_desc"].ToString()}";
                        row["table_desc_type"] = $"{DescType.DB}:{ row["db_name"]}";
                        dt.Rows.Add(row.ItemArray);
                    }
                    else
                    {
                        DataRow row = dtLocal.NewRow();
                        row["table_name"] = tableName;
                        row["table_desc"] = "ZZZ"; //不同库不同  Acceess：DESCRIPTION
                        row["db_name"] = dbName;
                        dt.Rows.Add(row.ItemArray);
                    }
                }
            }
            return dt;
        }
        public virtual DataTable GetTablesViewJoinLocalTables(DataTable dtTableSchema,DataTable dtViewSchema,DataTable dtLocal, string dbName)
        {
            DataTable dt = dtLocal.DefaultView.ToTable();
            dt.Rows.Clear();
            foreach (DataRow item in dtTableSchema.Rows)//从架构中获取的表信息
            {
                string tableName = item.Field<string>("TABLE_NAME");
                var rows = dtLocal.AsEnumerable().Where(t => t.Field<string>("table_name").ToLower() == tableName.ToLower() && t.Field<string>("db_name") == dbName).ToList();
                if (rows.Count() > 0) //本地库中含有说明
                {
                    rows[0]["table_name"] = tableName;
                    rows[0]["table_desc_type"] = dbName;
                    dt.Rows.Add(rows[0].ItemArray);
                }
                else
                {
                    rows = dtLocal.AsEnumerable().Where(t => t.Field<string>("table_name").ToLower() == tableName.ToLower()).ToList();
                    if (rows.Count() > 0)
                    {
                        DataRow row = rows[0];
                        row["id"] = "";
                        row["table_name"] = tableName;
                        row["table_desc"] = $"{row["table_desc"].ToString()}";
                        row["table_desc_type"] = $"{DescType.DB}:{ row["db_name"]}";
                        dt.Rows.Add(row.ItemArray);
                    }
                    else //在数据库架构和本地都没有说明
                    {
                        DataRow row = dtLocal.NewRow();
                        row["table_name"] = tableName;
                        string comment = GetTableComment(item);//item.Field<string>("TABLE_COMMENT");
                        if (string.IsNullOrEmpty(comment))
                        {
                            row["table_desc"] = "ZZZZ";
                            row["table_desc_type"] = "";
                        }
                        else
                        {
                            row["table_desc"] = comment;
                            row["table_desc_type"] = DescType.Schema.ToString();
                        }
                        row["db_name"] = dbName;
                        dt.Rows.Add(row.ItemArray);
                    }
                }
            }
            foreach (DataRow item in dtViewSchema.Rows)//从架构中获取的表信息
            {
                string tableName = item.Field<string>("TABLE_NAME");
                var rows = dtLocal.AsEnumerable().Where(t => t.Field<string>("table_name").ToLower() == tableName.ToLower() && t.Field<string>("db_name") == dbName).ToList();
                if (rows.Count() > 0) //本地库中含有说明
                {
                    rows[0]["table_name"] = tableName;
                    rows[0]["table_desc_type"] = "Views:"+dbName;
                    dt.Rows.Add(rows[0].ItemArray);
                }
                else
                {
                    rows = dtLocal.AsEnumerable().Where(t => t.Field<string>("table_name").ToLower() == tableName.ToLower()).ToList();
                    if (rows.Count() > 0)
                    {
                        DataRow row = rows[0];
                        row["id"] = "";
                        row["table_name"] = tableName;
                        row["table_desc"] = $"{row["table_desc"].ToString()}";
                        row["table_desc_type"] = $"Views:{DescType.DB}:{ row["db_name"]}";
                        dt.Rows.Add(row.ItemArray);
                    }
                    else //在数据库架构和本地都没有说明
                    {
                        DataRow row = dtLocal.NewRow();
                        row["table_name"] = tableName;
                        //string comment = GetTableComment(item);// item.Field<string>("TABLE_COMMENT");
                        row["table_desc"] = "Views";
                        row["table_desc_type"] = "Views";
                        row["db_name"] = dbName;
                        dt.Rows.Add(row.ItemArray);
                    }
                }
            }
            return dt;
        }
        public virtual DataTable GetDbColumns()
        {
            return GetDbSchema("Columns");
        }
        public DataTable GetTablesJoinLocalColumns(DataTable dtTableSchema, DataTable dtLocal, string dtLocalAddColName, DataTable dtSchemaCols, string dbName, string tabName, out int count, out double usetime)
        {
            DataTable dt = dtLocal.Copy();
            dt.Rows.Clear();
            count = dtSchemaCols.Rows.Count;
            usetime = UseTime;
            foreach (DataColumn dc in dtTableSchema.Columns)
            {
                string colName = dc.ColumnName;
                string colType = dc.DataType.Name;
                //架构中的字段信息
                DataRow rowSchema = dtSchemaCols.AsEnumerable().FirstOrDefault(t => t.Field<string>("table_name") == dtTableSchema.TableName && t.Field<string>("column_name") == colName);
                var rows = dtLocal.AsEnumerable().Where(t => t.Field<string>("col_name") == colName && t.Field<string>("db_name") == dbName && t.Field<string>("table_name") == tabName).ToList();
                if (rows.Count() == 1)  //以数据库为主查询
                {
                    rows[0]["sort_code"] = rowSchema == null ? 0 : rowSchema["ORDINAL_POSITION"];
                    rows[0]["data_type"] = colType;
                    rows[0][dtLocalAddColName] = rows[0]["table_name"].ToString();
                    dt.Rows.Add(rows[0].ItemArray);
                }
                else //不在数据库中，则查询架构
                {
                    DataRow row = dt.NewRow();
                    row["id"] = "";
                    row["sort_code"] = rowSchema == null ? 0 : rowSchema["ORDINAL_POSITION"];
                    row["col_name"] = colName;
                    row["data_type"] = colType;
                    string comment = GetColumnComment(rowSchema);
                    if (!string.IsNullOrEmpty(comment))  //架构不为空
                    {
                        row["col_desc"] = comment;
                        row[dtLocalAddColName] = DescType.Schema.ToString();
                    }
                    else
                    {
                        rows = dtLocal.AsEnumerable().Where(t => t.Field<string>("col_name") == colName).ToList();
                        if (rows.Count() > 0)
                        {
                            row["col_desc"] = rows[0]["col_desc"];
                            row[dtLocalAddColName] = $"{DescType.DB.ToString()}:{rows[0]["db_name"].ToString()}:{rows[0]["table_name"].ToString()}";
                        }
                        else
                        {
                            row["col_desc"] = "ZZZZ";
                            row[dtLocalAddColName] = "";
                        }
                    }
                    dt.Rows.Add(row);
                }
            }
            return dt;
        }
        protected virtual string GetColumnComment(DataRow row)
        {
            return "";
        }
        protected virtual string GetTableComment(DataRow row)
        {
            if (row == null)
                return "";
            return "";
        }

        public virtual DataTable GetDbViews(string tableSchema="")
        {
            return GetDbSchema("Views");        
        }
        public DataTable GetDbSchema(string schemaName)
        {
            DataTable dt = null;
            Stopwatch sw = Stopwatch.StartNew();
            try
            {
                Connection.Open();
                if (string.IsNullOrEmpty(schemaName))
                {
                    dt = Connection.GetSchema();
                }
                else
                {
                    dt = Connection.GetSchema(schemaName);
                    dt.TableName = schemaName;
                }
                _useTime = Math.Round(sw.Elapsed.TotalMilliseconds, 2);
            }
            catch (Exception ex)
            {
                throw new Exception($"【连接串】:{connection.ConnectionString} 【异常】{ex.Message}");
            }
            finally
            {
                Connection.Close();
                _useTime = Math.Round(sw.Elapsed.TotalMilliseconds, 2);
            }
            return dt;
        }
        public virtual DataTable GetDataTable(string tabName)
        {
            string strSql = $"select * from [{tabName}]";
            DbCommand cmd = GetSqlStringCommond(strSql);
            DataTable dt = ExecuteDataTable(cmd);
            dt.TableName = tabName;
            return dt;
        }

        public virtual DataTable GetDataTableBySql(string strSql,string tabName)
        {
           // string strSql = $"select * from [{tabName}]";
            DbCommand cmd = GetSqlStringCommond(strSql);
            DataTable dt = ExecuteDataTable(cmd);
            dt.TableName = tabName;
            return dt;
        }
        public virtual int GetTableRowsCount(string tabName)
        {
            string strSql = $"select count(*) from [{tabName}]";
            DbCommand cmd = GetSqlStringCommond(strSql);
            object ret=ExecuteScalar(cmd);
            if (ret == null)
            {
                return 0;
            }
            return Int32.Parse(ret.ToString());
        }
        //public virtual DataTable GetTopDataTable(string tabName,int topNum)
        //{
        //    string strSql = $"select top {topNum} *from {tabName}";
        //    DataTable dt = new DataTable();
        //    _semaphore.Wait(); //信号量 每次只允许一个线程通过
        //    System.Threading.Thread.Sleep(100);
        //    //DbCommand cmd = GetSqlStringCommond(strSql);
        //   // dt = ExecuteDataTable(cmd);
        //    //dt.TableName = tabName;
        //    _semaphore.Release();
        //    return dt;
        //}
        /// <summary>
        /// 将 <see cref="DataTable"/> 的数据批量插入到数据库中。
        /// </summary>
        /// <param name="dataTable">要批量插入的 <see cref="DataTable"/>。</param>
        /// <param name="batchSize">每批次写入的数据量。</param>
        
    }
}
